use tracing::{debug, error, info, warn};
use crate::db::query_statement::prelude::{*};
use crate::error::*;
use crate::{*};
use serde::{Deserialize, Serialize};


pub fn convert_cols_maker_to_sql(cols:Option<ColumnSqlMaker>) -> String{
    match cols {
        Some(c) => {
            let maker = c.try_to_field_maker().unwrap();
            if maker.is_empty() {
                return "*".to_owned();
            }
            return maker.get_sql();
        },
        None => {
            "*".to_owned()
        }
    }
}

pub fn convert_order_field_to_sql(order:Option<&str>) -> String{
    match order {
        Some(c) => {
          format!("ORDER BY {}",c)
        },
        None => {
            "".to_owned()
        }
    }
}

pub fn convert_group_field_to_sql(group:Option<&str>,having:Option<&str>) -> String{
    match group {
        Some(g) => {
          let mut sql = format!("GROUP BY {}",g);
            match having {
                Some(h) => {
                    sql += " HAVING ";
                    sql += h;
                },
                _ => {},
            }
          sql
        },
        None => {
            "".to_owned()
        }
    }
}

pub trait ModelQuery{

    const TABLE_NAME_TPL:&'static str;

    const TABLE_PK_NAME:&'static str;

    fn get_table_tpl() -> &'static str{
        Self::TABLE_NAME_TPL
    }

    fn get_table_pk_name() -> &'static str{
        Self::TABLE_PK_NAME
    }

    fn find_by_pk(id:i64,filter:Option<WhereSqlMaker>) -> Result<SqlMakerRaw>{
        let pk = Self::get_table_pk_name();
        let mut condition = lavender_query_filter!(pk id);
        if let Some(f) = filter {
            condition = condition.add(f);
        }
        let sql = format!("SELECT * FROM {} WHERE {} LIMIT 1;",Self::get_table_tpl(),condition.get_sql());
        Ok(SqlMakerRaw::with(sql, condition.get_bind().unwrap()))
    }

    fn find(filter:WhereSqlMaker,cols:Option<ColumnSqlMaker>,order:Option<&str>) -> Result<SqlMakerRaw>{
        let condition_sql;
        let replace = filter.get_bind();
        if filter.is_empty() {
            warn!("do query find. but filter is empty");
            condition_sql = "1".to_owned();
        }
        else{
            condition_sql = filter.get_sql();
        }        
        
        let sql = format!("SELECT {} FROM {} WHERE {} {} LIMIT 1;",
            convert_cols_maker_to_sql(cols),
            Self::get_table_tpl(),
            condition_sql,
            convert_order_field_to_sql(order)
        );

        if let Some(bind) = replace {
            return Ok(SqlMakerRaw::with(sql, bind));
        }
        Ok(SqlMakerRaw::with_sql(sql))
    }

    fn find_all(filter:WhereSqlMaker,cols:Option<ColumnSqlMaker>,order:Option<&str>,offset:Option<isize>,length:Option<isize>) -> Result<SqlMakerRaw>{
        let condition_sql;
        let mut replace = filter.get_bind().unwrap_or_default();
        if filter.is_empty() {
            warn!("do query find. but filter is empty");
            condition_sql = "1".to_owned();
        }
        else{
            condition_sql = filter.get_sql();
        }
        let mut limit_sql = "".to_owned();
        if offset.is_some(){
            limit_sql = "LIMIT ?".to_owned();
            replace.push(offset.unwrap().into());
            if length.is_some(){
                limit_sql += ",?"; 
                replace.push(length.unwrap().into());
            }
        }

        let sql = format!("SELECT {} FROM {} WHERE {} {} {};",
            convert_cols_maker_to_sql(cols),
            Self::get_table_tpl(),
            condition_sql,
            convert_order_field_to_sql(order),
            limit_sql
        );
        
        return Ok(SqlMakerRaw::with(sql, replace));
        
        
    }

    fn select(filter:WhereSqlMaker,cols:Option<ColumnSqlMaker>,order:Option<&str>,offset:Option<isize>,length:Option<isize>,group:Option<&str>,having:Option<&str>) -> Result<SqlMakerRaw>{
        let condition_sql;
        let mut replace = filter.get_bind().unwrap_or_default();
        if filter.is_empty() {
            warn!("do query find. but filter is empty");
            condition_sql = "1".to_owned();
        }
        else{
            condition_sql = filter.get_sql();
        }
        let mut limit_sql = "".to_owned();
        if offset.is_some(){
            limit_sql = "LIMIT ?".to_owned();
            replace.push(offset.unwrap().into());
            if length.is_some(){
                limit_sql += ",?"; 
                replace.push(length.unwrap().into());
            }
        }

        let sql = format!("SELECT {} FROM {} WHERE {} {} {} {};",
            convert_cols_maker_to_sql(cols),
            Self::get_table_tpl(),
            condition_sql,
            convert_order_field_to_sql(order),
            convert_group_field_to_sql(group,having),
            limit_sql
        );
        
        return Ok(SqlMakerRaw::with(sql, replace));
    }

    fn add(cols:ColumnSqlMaker) -> Result<SqlMakerRaw>{
        let maker = cols.try_to_insert_maker()?;
        if maker.is_empty(){
            warn!("do query add. but data is empty");
        }
        let sql = format!("INSERT INTO {}{};",Self::get_table_tpl(),maker.get_sql());
        return Ok(SqlMakerRaw::with(sql, maker.get_bind().unwrap_or_default()));
    }

    fn update(id:i64,update:ColumnSqlMaker,filter:Option<WhereSqlMaker>) -> Result<SqlMakerRaw>{
        let pk = Self::get_table_pk_name();
        let mut condition = lavender_query_filter!(pk id);
        if let Some(f) = filter {
            condition = condition.add(f);
        }
        let maker = update.try_to_update_maker()?;
        if maker.is_empty(){
            warn!("do query update. but data is empty");
        }
        let mut replace = maker.get_bind().unwrap_or_default();
        replace.extend(condition.get_bind().unwrap());
        let sql = format!("UPDATE {} SET {} WHERE {} LIMIT 1;",Self::get_table_tpl(),maker.get_sql(),condition.get_sql());
        return Ok(SqlMakerRaw::with(sql, replace));
    }

    fn update_all(filter:WhereSqlMaker,update:ColumnSqlMaker) -> Result<SqlMakerRaw>{
        let maker = update.try_to_update_maker()?;
        if maker.is_empty(){
            warn!("do query update_all. but data is empty");
        }
        if filter.is_empty(){
            warn!("do query update_all. but filter is empty");
        }
        let mut replace = maker.get_bind().unwrap_or_default();
        if !filter.is_bind_empty(){
            replace.extend(filter.get_bind().unwrap());
        }

        let sql = format!("UPDATE {} SET {} WHERE {} ;",Self::get_table_tpl(),maker.get_sql(),filter.get_sql());
        return Ok(SqlMakerRaw::with(sql, replace));
    }

    fn upsert(insert:ColumnSqlMaker,update:ColumnSqlMaker) -> Result<SqlMakerRaw>{
        let insert_maker = insert.try_to_insert_maker()?;
        if insert_maker.is_empty(){
            warn!("do query upsert. but insert data is empty");
        }
        let update_maker = update.try_to_update_maker()?;
        if update_maker.is_empty(){
            warn!("do query upsert. but update data is empty");
        }
        let mut replace = insert_maker.get_bind().unwrap_or_default();
        if !update_maker.is_bind_empty(){
            replace.extend(update_maker.get_bind().unwrap());
        }
        let sql = format!("INSERT INTO {}{} ON DUPLICATE KEY UPDATE {};",Self::get_table_tpl(),insert_maker.get_sql(),update_maker.get_sql());
        return Ok(SqlMakerRaw::with(sql, replace));
    }

    fn delete(id:i64,filter:Option<WhereSqlMaker>)  -> Result<SqlMakerRaw>{
        let pk = Self::get_table_pk_name();
        let mut condition = lavender_query_filter!(pk id);
        if let Some(f) = filter {
            condition = condition.add(f);
        }
        let sql = format!("DELETE FROM {} WHERE {} LIMIT 1;",Self::get_table_tpl(),condition.get_sql());
        Ok(SqlMakerRaw::with(sql, condition.get_bind().unwrap()))
    }

    fn delete_all(filter:WhereSqlMaker,limit:Option<isize>)  -> Result<SqlMakerRaw>{
        if filter.is_empty(){
            warn!("do delete_all update_all. but filter is empty");
        }
        let mut replace = filter.get_bind().unwrap();
        let mut limit_sql = "".to_owned();
        if let Some(num) = limit{
            limit_sql = "LIMIT ?".to_owned();
            replace.push(num.into());
        }
        let sql = format!("DELETE FROM {} WHERE {} {};",Self::get_table_tpl(),filter.get_sql(),limit_sql);
        Ok(SqlMakerRaw::with(sql, replace))
    }

    fn bulk_insert(data:BulkInsertSqlMaker) -> Result<SqlMakerRaw>{
        let mut data_maker = data.try_to_raw_maker()?;
        let full_tpl = format!("INSERT INTO {} {};",Self::get_table_tpl(),data_maker.get_sql());
        data_maker.set_sql(full_tpl);
        Ok(data_maker)
    }

}

#[derive(Debug, Clone,Default, Serialize, Deserialize)]
pub struct BaseModel{

}

impl ModelQuery for BaseModel {
    const TABLE_NAME_TPL:&'static str = "{table}";

    const TABLE_PK_NAME:&'static str = "id";
}

pub mod prelude {
    pub use super::{
        ModelQuery, 
        BaseModel
    };
}

#[macro_export]
macro_rules! lavender_model_query {
    (find_by_pk $id:tt,_) => {
        $crate::db::model::BaseModel::find_by_pk($id as i64, Option::<$crate::db::query_statement::WhereSqlMaker>::None)
    };
    (find_by_pk $id:tt,$filter:expr) => {
        $crate::db::model::BaseModel::find_by_pk($id as i64,Some($filter)) 
    };
    (find_by_pk $id:tt,$filter:expr) => {
        lavender_model_query!(find_by_pk $id,$filter);
    };
    (find_by_pk $id:tt) => {
        lavender_model_query!(find_by_pk $id,_);
    };

    (find $filter:expr,_,_) => {
        $crate::db::model::BaseModel::find($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Option::<&str>::None)
    };
    (find $filter:expr,_,$order:tt) => {
        $crate::db::model::BaseModel::find($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order))
    }; 
    (find $filter:expr,$cols:expr,_) => {
        $crate::db::model::BaseModel::find($filter,Some($cols),Option::<&str>::None)
    };

    (find $filter:expr,$cols:expr,$order:tt) => {
        $crate::db::model::BaseModel::find($filter,Some($cols),Some($order))
    }; 
    (find $filter:expr,$cols:expr) => {
        lavender_model_query!(find $filter,$cols,_);
    };
    (find $filter:expr) => {
        lavender_model_query!(find $filter,_,_);
    };


    // (find_all $filter:expr,_,_,_,_) => {
    //     $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Option::<&str>::None,Option::<isize>::None,Option::<isize>::None)
    // };
    // (find_all $filter:expr,$cols:expr,_,_,_) => {
    //     $crate::db::model::BaseModel::find_all($filter,Some($cols),Option::<&str>::None,Option::<isize>::None,Option::<isize>::None)
    // };
    // (find_all $filter:expr,_,$order:tt,_,_) => {
    //     $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order),Option::<isize>::None,Option::<isize>::None)
    // };
    // (find_all $filter:expr,_,$order:tt,$offset:tt,_) => {
    //     $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order),Some($offset as isize),Option::<isize>::None)
    // };
    // (find_all $filter:expr,_,$order:tt,$offset:tt,$length:tt) => {
    //     $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order),Some($offset as isize ),Some($length as isize))
    // };
    // (find_all $filter:expr,$cols:expr,$order:tt,_,_) => {
    //     $crate::db::model::BaseModel::find_all($filter,Some($cols),Some($order),Option::<isize>::None,Option::<isize>::None)
    // };
    // (find_all $filter:expr,$cols:expr,$order:tt,$offset:tt,_) => {
    //     $crate::db::model::BaseModel::find_all($filter,Some($cols),Some($order),Some($offset as isize),Option::<isize>::None)
    // };
    // (find_all $filter:expr,$cols:expr,$order:tt,$offset:tt,$length:tt) => {
    //     $crate::db::model::BaseModel::find_all($filter,Some($cols),Some($order),Some($offset as isize),Some($length as isize))
    // };
    (@find_all_cofl $filter:expr,$cols:expr,$order:tt,$offset:tt,$length:tt) => {
        $crate::db::model::BaseModel::find_all($filter,Some($cols),Some($order),Some($offset as isize ),Some($length as isize))
    };
    (@find_all_ofl $filter:expr,$order:tt,$offset:tt,$length:tt) => {
        $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order),Some($offset as isize ),Some($length as isize))
    };
    (@find_all_of $filter:expr,$order:tt,$offset:tt) => {
        $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order),Some($offset as isize ),Option::<isize>::None)
    };
    (@find_all_cof $filter:expr,$cols:expr,$order:tt,$offset:tt) => {
        $crate::db::model::BaseModel::find_all($filter,Some($cols),Some($order),Some($offset as isize ),Option::<isize>::None)
    };
    (@find_all_o $filter:expr,$order:tt) => {
        $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Some($order),Option::<isize>::None,Option::<isize>::None)
    };
    (@find_all_co $filter:expr,$cols:expr,$order:tt) => {
        $crate::db::model::BaseModel::find_all($filter,Some($cols),Some($order),Option::<isize>::None,Option::<isize>::None)
    };
    (@find_all_c $filter:expr,$cols:expr) => {
        $crate::db::model::BaseModel::find_all($filter,Some($cols),Option::<&str>::None,Option::<isize>::None,Option::<isize>::None)
    };
    (@find_all_w $filter:expr) => {
        $crate::db::model::BaseModel::find_all($filter,Option::<$crate::db::query_statement::ColumnSqlMaker>::None,Option::<&str>::None,Option::<isize>::None,Option::<isize>::None)
    };
    (find_all $filter:expr,_,$order:tt,$offset:tt,$length:tt) => {
        lavender_model_query!(@find_all_ofl $filter,$order,$offset)
    };
    (find_all $filter:expr,$cols:expr,$order:tt,$offset:tt,$length:tt) => {
        lavender_model_query!(@find_all_cofl $filter,$cols,$order,$offset)
    };
    (find_all $filter:expr,_,$order:tt,$offset:tt) => {
        lavender_model_query!(@find_all_of $filter,$order,$offset)
    };
    (find_all $filter:expr,$cols:expr,$order:tt,$offset:tt) => {
        lavender_model_query!(@find_all_cof $filter,$cols,$order,$offset)
    };
    (find_all $filter:expr,_,$order:tt) => {
        lavender_model_query!(@find_all_o $filter,$order)
    };
    (find_all $filter:expr,$cols:expr,$order:tt) => {
        lavender_model_query!(@find_all_co $filter,$cols,$order)
    };
    (find_all $filter:expr,$cols:expr) => {
        lavender_model_query!(@find_all_c $filter,$cols)
    };
    (find_all $filter:expr) => {
        lavender_model_query!(@find_all_w $filter)
    };

    (add_bulk $data:expr) => {
        $crate::db::model::BaseModel::bulk_insert($data)
    };
    (add $data:expr) => {
        $crate::db::model::BaseModel::add($data)
    };

    (update $id:tt,$data:expr,_) => {
        $crate::db::model::BaseModel::update($id as i64,$data,Option::<$crate::db::query_statement::WhereSqlMaker>::None)
    };
    (update $id:tt,$data:expr,$filter:expr) => {
        $crate::db::model::BaseModel::update($id as i64,$data,Some($filter))
    };
    (update $id:tt,$data:expr) => {
        lavender_model_query!(update $id,$data,_)
    };
    (update_all $filter:expr,$data:expr) => {
        $crate::db::model::BaseModel::update_all($filter,$data)
    };
    (upsert $insert:expr,$update:expr) => {
        $crate::db::model::BaseModel::upsert($insert,$update)
    };
    (delete $id:tt,_) => {
        $crate::db::model::BaseModel::delete($id as i64,None)
    };
    (delete $id:tt,$filter:expr) => {
        $crate::db::model::BaseModel::delete($id  as i64,Some($filter))
    };
    (delete $id:tt) => {
        lavender_model_query!(delete $id,_)
    };
    (delete_all $filter:expr,_) => {
        $crate::db::model::BaseModel::delete_all($filter,Option::<isize>::None)
    };
    (delete_all $filter:expr,$limit:tt) => {
        $crate::db::model::BaseModel::delete_all($filter,Some($limit as isize))
    };
    (delete_all $filter:expr) => {
        lavender_model_query!(delete_all $filter,_)
    };    
}